package com.ziyun.pop.modules.storeMember.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ziyun.pop.modules.storeMember.entity.*;
import com.ziyun.pop.modules.storeMember.vo.StoreMemberEntityVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 门店会员关联表
 * 
 * @author jiangli
 * @email 
 * @date 2018-09-21 15:47:40
 */
public interface StoreMemberDao extends BaseMapper<StoreMemberEntity> {

    List<StoreMemberVO> list(@Param("page") IPage page,@Param("map")  Map<String, Object> map);

    List<UserBaseInfoEntity> selectByMobileName(@Param("name")String name,@Param("mobile")String mobile);

    Integer selectReferralsCode(Integer sellerId);

    StoreMemberVO getOneById(Long sid);

    BigDecimal getAdvance(Integer memberId);

    Integer addAdvance(@Param("memberId")Integer memberId,@Param("count") BigDecimal count);

    Integer getReferralsCode(Integer sellerId);

    StoreMemberEntity getByName(String name);

    Integer getStoreMemberVOByName(String name);

    List<Map<Long,String>> getUsernames(Integer sellerId);

    @Update("UPDATE store_member SET price_set=NULL WHERE sid=#{sid}")
    int cancel(Long sid);

    @Select("SELECT COUNT(1) FROM store_member WHERE seller_id=#{sellerId} AND DATE_SUB(CURDATE(), INTERVAL 5 DAY) <= DATE(create_time)")
    int getCountIn5(Integer sellerId);

    @Select("SELECT COUNT(1) FROM store_member WHERE seller_id=#{sellerId} AND DATE_SUB(CURDATE(), INTERVAL 5 DAY) <= DATE(create_time)  AND merchants_source=0")
    int getCountIn5Mall(Integer sellerId);

    @Select("SELECT COUNT(1) FROM store_member WHERE seller_id=#{sellerId} AND DATE_SUB(CURDATE(), INTERVAL 5 DAY) <= DATE(create_time)  AND merchants_source=1")
    int getCountIn5Twb(Integer sellerId);

    List<StoreMemberEntityVo> getMemberByName(@Param("map") Map<String,Object> params);
    //客户列表
    List<Customer> customer(@Param("page") IPage page,@Param("map")  Map<String, Object> map);
    //近7天新增
    List<CustomerWeek> customerWeek(Integer sellerId);
    //在线
    @Select("SELECT COUNT(DISTINCT sm.member_id) FROM store_member sm LEFT JOIN erp_order eo ON sm.member_id = eo.member_id " +
            "WHERE sm.merchants_source = 0 AND sm.seller_id = #{sellerId} AND eo.order_no IS NOT NULL AND eo.production_status != 11")
    Integer onlineCount(Integer sellerId);
    //到店
    @Select("SELECT COUNT(DISTINCT sm.name) FROM store_member sm LEFT JOIN erp_order eo ON sm.`name` = eo.member_name  AND sm.seller_id = eo.third_merchant_id " +
            "WHERE sm.merchants_source = 1 AND sm.seller_id = #{sellerId} AND eo.member_name IS NOT NULL AND eo.production_status != 11")
    Integer offlineCount(Integer sellerId);
    //复购
    @Select("SELECT COUNT(1) FROM (SELECT DISTINCT a.`name` FROM store_member a LEFT JOIN erp_order b ON a.`member_id` = b.`member_id` AND a.`seller_id` = b.`third_merchant_id`" +
            "AND a.`name` = b.member_name WHERE a.`seller_id` = #{sellerId} AND b.`del` = 0 AND b.production_status != 11 AND b.`order_no` IS NOT NULL GROUP BY a.`name` HAVING COUNT(*) > 1 ) o")
    Integer repeatBuy(Integer sellerId);
    // 线上新客户
    @Select("SELECT COUNT(DISTINCT a.member_id) FROM store_member a INNER JOIN erp_order e ON a.member_id = e.member_id " +
            "WHERE a.seller_id = #{sellerId} AND a.merchants_source = 0 AND YEARWEEK(date_format(a.create_time, '%Y-%m-%d'),1) = YEARWEEK(NOW(), 1) AND e.member_id IS NOT NULL AND e.production_status != 11")
    int onlineNewCount(Integer sellerId);
    // 线下新客户
    @Select("SELECT COUNT(DISTINCT a.`name`) FROM store_member a INNER JOIN erp_order e ON a.`name` = e.member_name AND a.seller_id = e.third_merchant_id " +
            "WHERE a.seller_id = #{sellerId} AND a.merchants_source = 1 AND YEARWEEK(date_format(a.create_time, '%Y-%m-%d'),1) = YEARWEEK(NOW(), 1) AND e.member_name IS NOT NULL AND e.production_status != 11")
    int offlineNewCount(Integer sellerId);

    int logicDelete(List<Long> sids);
}
